#!/opt/local/bin/perl
use strict;
use Data::Dumper;
use WWW::Mechanize;
use LWP::UserAgent;
use HTML::LinkExtor;
use XML::DOM;
use URI::URL;
use DBI;

my $sDbFn = 'veganiseasy.sqlite';

# Clear out any previous database
unlink($sDbFn) or die("Failed to delete old database file, $sDbFn. $!") if (-e $sDbFn);

# Connect to the new database file
my $oDbh = DBI->connect("dbi:SQLite:dbname=$sDbFn","","") or die("Failed to open database file, $!");

# First, get a list of all cities
my $oInitialUrl   = url("http://www.barnivore.com/")->abs;
my $sInitialHost  = $oInitialUrl->host;
my $iLinksVisited = 0;
my $phLinksSeen   = {$oInitialUrl => 1};
my @aLinksToVisit = ($oInitialUrl); 

my $oUserAgent = LWP::UserAgent->new; 
$oUserAgent->agent('Mozilla/5.0');
sub fetchUrl
{
my ($sUrl) = @_;
my $oRequest = $oUserAgent->simple_request(HTTP::Request->new("GET", $sUrl));
return $oRequest->content;
}

sub extractLinks
{
my ($sBaseUrl, $sHtml) = @_;
my @aLinks;
my $oExtor = HTML::LinkExtor->new(sub {
    my ($sTag, %hAttr) = @_;
    return if lc($sTag) ne 'a';
    my $sHref = $hAttr{'href'};
    if (defined($sHref))
      {
      push(@aLinks, url($sHref, $sBaseUrl)->abs);
      }
});

$oExtor->parse($sHtml);
return \@aLinks;
}

my $phKnownAttributes = {};
sub parsePage
{
my ($sUrl, $sContent) = @_;

if ($sContent !~ m/<company/)
  {
  print "Failed to parse page, $sUrl\n";
  return;
  }

my $oDoc = XML::DOM::Parser->new()->parse($sContent);
my $oCompany = $oDoc->getElementsByTagName('company')->item(0);
my $oUrlNode = $oDoc->createElement("barnivore-company-url");
$oUrlNode->addText($sUrl);
$oCompany->appendChild($oUrlNode);
addXmlNodeToDatabase($oCompany);
$oDoc->dispose();
}

sub addXmlNodeToDatabase
{
my ($oNode) = @_;
return if $oNode->getNodeType() != ELEMENT_NODE;

my $sNodeName = $oNode->getNodeName();
#print "Called with NODENAME=$sNodeName\n";
my $oChildNodes = $oNode->getChildNodes();
my $iChildCount = $oChildNodes->getLength();
my $phRow = {};
for (my $i=0; $i<$iChildCount; $i++)
  {
  my $oChildNode = $oChildNodes->item($i);
  next if $oChildNode->getNodeType() != ELEMENT_NODE;
  next if !$oChildNode->hasChildNodes();
  my $sChildNodeName = $oChildNode->getNodeName();
  $sChildNodeName =~ s/-/_/g;
  my $oTypeAttr = $oChildNode->getAttributeNode("type");
  my $sType = defined($oTypeAttr) ? $oTypeAttr->getNodeValue() : 'text';
  if ($sType eq 'array')
    {
    my $oArrayChildNodes = $oChildNode->getChildNodes();
    my $iArrayValueCount = $oArrayChildNodes->getLength();
    for (my $i=0; $i<$iArrayValueCount; $i++)
      {
      my $oArrayValueNode = $oArrayChildNodes->item($i);
      next if $oArrayValueNode->getNodeType() != ELEMENT_NODE;
      addXmlNodeToDatabase($oArrayValueNode);
      }
    }
  else
    {
    $phRow->{$sChildNodeName} = $oChildNode->getChildNodes()->item(0)->getNodeValue();
    my $sDataType = $sType eq 'integer' ? 'number' : 'text';

    # Create the table if necessary
    if (!defined($phKnownAttributes->{$sNodeName}))
      {
      my $sSql = "CREATE TABLE \"$sNodeName\" (\"$sChildNodeName\" $sDataType)";
#      print $sSql;
      $oDbh->do($sSql) or die("Failed to create table.  SQL:$sSql\nError:".$oDbh->errstr());
      $phKnownAttributes->{$sNodeName}->{$sChildNodeName} = 1;
      }
    elsif (!$phKnownAttributes->{$sNodeName}->{$sChildNodeName})
      {
      my $sSql = "ALTER TABLE \"$sNodeName\" ADD COLUMN \"$sChildNodeName\" $sDataType";
#      print $sSql;
      $oDbh->do($sSql) or die("Failed to alter table.  SQL:$sSql\nError:".$oDbh->errstr());
      $phKnownAttributes->{$sNodeName}->{$sChildNodeName} = 1;
      }
    }
  }
return if scalar keys %$phRow < 1;
# Insert the row into the database
my $sSql = '
    INSERT INTO "'.$sNodeName.'"
              ('.join(',',map {"\"$_\""} sort keys %$phRow).')
         VALUES
              ('.join(',',map {$oDbh->quote($phRow->{$_})} sort keys %$phRow).')
';
#print $sSql;
$oDbh->do($sSql) or die("Failed to insert row.  SQL:$sSql\nError:".$oDbh->errstr());
}

while (my $sUrl = pop(@aLinksToVisit))
  {
  print "Visiting $sUrl\n";
  $iLinksVisited++;

  if ($iLinksVisited > 2000)
    {
    # Assume something is wrong if we've visited this many pages
    last;
    }

  my $sContent = fetchUrl($sUrl);
  parsePage($sUrl, $sContent);
  my $paLinks = extractLinks($sUrl, $sContent);
  
  foreach my $oLink (@$paLinks)
    {
    next if $oLink->scheme !~ m/^http/;

    if ($oLink->host eq $sInitialHost)
      {
      my $sLink = $oLink->as_string;
      if (!$phLinksSeen->{$sLink})
        {
        print "Adding new link $sLink\n";
        push(@aLinksToVisit, $sLink);
        $phLinksSeen->{$sLink} = 1;
        }
      }
    }
  }

